1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmAdvanceEntryReport
4 Sub fillStaff()
5 Try
6 Dim CN As New SqlConnection(cs)
7 CN.Open()
8 adp = New SqlDataAdapter()
9 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(StaffName) FROM Staff,AdvanceEntry where Staff.St_ID=AdvanceEntry.StaffID", CN)
10 ds = New DataSet("ds")
11 adp.Fill(ds)
12 dtable = ds.Tables(0)
13 cmbStaffName.Items.Clear()
14 For Each drow As DataRow In dtable.Rows
15 cmbStaffName.Items.Add(drow(0).ToString())
16 Next
17
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22
23 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
24 fillStaff()
25 End Sub
26 Sub Reset()
27 cmbStaffName.SelectedIndex = -1
28 DateTimePicker1.Text = Now
29 DateTimePicker2.Text = Today
30 dtpDateFrom.Text = Today
31 dtpDateTo.Text = Now
32 fillStaff()
33 End Sub
34 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
35 Reset()
36 End Sub
37
38
39 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
40 Me.Close()
41 End Sub
42
43 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
44 Try
45 If Len(Trim(cmbStaffName.Text)) = 0 Then
46 MessageBox.Show("Please select Staff name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
47 cmbStaffName.Focus()
48 Exit Sub
49 End If
50 Cursor = Cursors.WaitCursor
51 Timer1.Enabled = True
52 Dim rpt As New rptAdvanceEntry 'The report you created.
53 Dim myConnection As SqlConnection
54 Dim MyCommand As New SqlCommand()
55 Dim myDA As New SqlDataAdapter()
56 Dim myDS As New DataSet 'The DataSet you created.
57 myConnection = New SqlConnection(cs)
58 MyCommand.Connection = myConnection
59 MyCommand.CommandText = "SELECT AdvanceEntry.Id, AdvanceEntry.StaffID, AdvanceEntry.Amount, AdvanceEntry.Deduction, AdvanceEntry.WorkingDate, Staff.St_ID, Staff.StaffID AS Expr1, Staff.StaffName, Staff.DateOfJoining, Staff.Gender,Staff.FatherName, Staff.TemporaryAddress, Staff.PermanentAddress, Staff.Designation, Staff.Qualifications, Staff.DOB, Staff.PhoneNo, Staff.MobileNo, Staff.Email, Staff.Photo, Staff.ClassType, Staff.SchoolID,Staff.AccountName, Staff.AccountNumber, Staff.Bank, Staff.Branch, Staff.IFSCcode, Staff.Status, Staff.Salary FROM AdvanceEntry INNER JOIN Staff ON AdvanceEntry.StaffID = Staff.St_ID where WorkingDate between @d1 and @d2 and StaffName=@d3 and Amount > 0 order by WorkingDate"
60 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = dtpDateFrom.Value.Date
61 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = dtpDateTo.Value
62 MyCommand.Parameters.Add("@d3", SqlDbType.NChar, 200, "Name").Value = cmbStaffName.Text
63 MyCommand.CommandType = CommandType.Text
64 myDA.SelectCommand = MyCommand
65 myDA.Fill(myDS, "Staff")
66 myDA.Fill(myDS, "AdvanceEntry")
67 rpt.SetDataSource(myDS)
68 rpt.SetParameterValue("v1", dtpDateFrom.Value.Date)
69 rpt.SetParameterValue("v2", dtpDateTo.Value.Date)
70 frmReport.CrystalReportViewer1.ReportSource = rpt
71 frmReport.ShowDialog()
72 Catch ex As Exception
73 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
74 End Try
75 End Sub
76
77 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
78 Try
79 Cursor = Cursors.WaitCursor
80 Timer1.Enabled = True
81 Dim rpt As New rptAdvanceEntry 'The report you created.
82 Dim myConnection As SqlConnection
83 Dim MyCommand As New SqlCommand()
84 Dim myDA As New SqlDataAdapter()
85 Dim myDS As New DataSet 'The DataSet you created.
86 myConnection = New SqlConnection(cs)
87 MyCommand.Connection = myConnection
88 MyCommand.CommandText = "SELECT AdvanceEntry.Id, AdvanceEntry.StaffID, AdvanceEntry.Amount, AdvanceEntry.Deduction, AdvanceEntry.WorkingDate, Staff.St_ID, Staff.StaffID AS Expr1, Staff.StaffName, Staff.DateOfJoining, Staff.Gender,Staff.FatherName, Staff.TemporaryAddress, Staff.PermanentAddress, Staff.Designation, Staff.Qualifications, Staff.DOB, Staff.PhoneNo, Staff.MobileNo, Staff.Email, Staff.Photo, Staff.ClassType, Staff.SchoolID,Staff.AccountName, Staff.AccountNumber, Staff.Bank, Staff.Branch, Staff.IFSCcode, Staff.Status, Staff.Salary FROM AdvanceEntry INNER JOIN Staff ON AdvanceEntry.StaffID = Staff.St_ID where WorkingDate between @d1 and @d2 and Amount > 0 order by WorkingDate"
89 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateTimePicker2.Value.Date
90 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTimePicker1.Value
91 MyCommand.CommandType = CommandType.Text
92 myDA.SelectCommand = MyCommand
93 myDA.Fill(myDS, "Staff")
94 myDA.Fill(myDS, "AdvanceEntry")
95 rpt.SetDataSource(myDS)
96 rpt.SetParameterValue("v1", DateTimePicker2.Value.Date)
97 rpt.SetParameterValue("v2", DateTimePicker1.Value.Date)
98 frmReport.CrystalReportViewer1.ReportSource = rpt
99 frmReport.ShowDialog()
100 Catch ex As Exception
101 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
102 End Try
103 End Sub
104
105 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
106 Cursor = Cursors.Default
107 Timer1.Enabled = False
108 End Sub
109 End Class
